package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class LabTestCategoryDAO {
	private static final String CREATE_LAB_TEST_CATEGORY = "INSERT INTO qdlc_lab_test_category (categoryName) VALUES (?)";
	private static final String UPDATE_LAB_TEST_CATEGORY = "UPDATE qdlc_lab_test_category SET categoryName = ? WHERE categoryId = ?";
	private static final String GET_LAB_TEST_CATEGORY_FROM_ID = "SELECT * FROM qdlc_lab_test_category WHERE categoryId = ?;";
	private static final String GET_ALL_LAB_TEST_CATEGORY = "SELECT * FROM qdlc_lab_test_category;";
	
	public LabTestCategory createLabTestCategory(String categoryName) {
        LabTestCategory cat = null;

        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(CREATE_LAB_TEST_CATEGORY,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            st.setString(1, categoryName);

            int affectedRows = st.executeUpdate();

            // Don't attempt to get keys if there is no affected row
            if (affectedRows > 0) {
                ResultSet keys = st.getGeneratedKeys();

                // Get key, return newUser
                if (keys.next()) {
                    int categoryId = keys.getInt(1);
                    cat = new LabTestCategory();
                    cat.setCategoryId(categoryId);
                    cat.setCategoryName(categoryName);
                    
                    if (keys != null) {
                        keys.close();
                    }
                }
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return cat;
	}

    public void updateCategory(String categoryName, int categoryId) {
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        try {
            PreparedStatement st = conn.prepareStatement(UPDATE_LAB_TEST_CATEGORY);
            st.setString(1, categoryName);
            st.setInt(2, categoryId);
            
            
            st.executeUpdate();

            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }

    public LabTestCategory getLabTestCategory(int categoryId) {
    	
       LabTestCategory cat = null;
    
        // TODO: Add exceptions
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_LAB_TEST_CATEGORY_FROM_ID);

            st.setInt(1, categoryId);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
               cat = new LabTestCategory();
               cat.setCategoryId(rs.getInt("categoryId"));
               cat.setCategoryName(rs.getString("categoryName"));
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return cat;
    }
    
    public ArrayList<LabTestCategory> getAllLabTestCategory() {
        ArrayList<LabTestCategory> catList = new ArrayList<LabTestCategory>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_ALL_LAB_TEST_CATEGORY);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
            	LabTestCategory cat = new LabTestCategory();
            	
            	cat.setCategoryId(rs.getInt("categoryId"));
            	cat.setCategoryName(rs.getString("categoryName"));
            	
            	catList.add(cat);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return catList;
    }
		
}
